package org.six11.olive3.server;

/**
 * 
 * 
 * @author Gabe Johnson <johnsogg@cmu.edu>
 */
public abstract class SqlStrings {

  // 1: user.name
  public final static String FIND_USER_BY_NAME = //
  "select id, name, pass from user where name=?";

  // 1: user.name
  // 2: user.pass
  public final static String INSERT_USER = // 
  "insert into user (name, pass) values (?, ?)";

  // 1: description.text
  // 2: description.user_id
  public static final String INSERT_DESCRIPTION = //
  "insert into description (text, user_id) values (?, ?)";

  // 1: description.id
  public static final String FIND_DESCRIPTION = "SELECT id, text, sketch_id, user_id from "
      + "description where id=?";

  // 1: sketch.id
  public static final String FIND_SKETCH = "select id, data, description_id, user_id from " + //
      "sketch where id = ?";

  // 1: sketch.id
  public static final String FIND_SKETCH_PARENT = //
    "select description_id from sketch where id = ?";
  
  // 1: user.id
  // 2. description.id
  // 3. sketch.data
  public static final String INSERT_SKETCH_FOR_DESCRIPTION = "insert into sketch (user_id, "
      + " description_id, data) values (?, ?, ?)";

  // 1: user.id
  // 2: sketch.id
  // 3: description.text
  public static final String INSERT_DESCRIPTION_FOR_SKETCH = "insert into description " + //
      "(user_id, sketch_id, text) values (?, ?, ?)";

  // 1: user.id
  //
  // This gives a list of description IDs the user did not make.
  public static final String FIND_DESCRIPTIONS_NOT_BY_USER = //
  "select id from description where user_id <> ?";

  // 1: user.id
  // 2: comma separated list of description.id
  public static final String FIND_DESCRIPTIONS_BY_USER_DISQUALIFY_CHILDREN = //
  "select distinct description_id from sketch where user_id = ? and description_id in (?)";

  // 1: user.id
  // 2: comma separated list of description.id
  public static final String FIND_DESCRIPTIONS_BY_USER_DISQUALIFY_PARENTS = //
  "select distinct d.id from description as d join sketch as s on s.description_id = d.id "
      + "where s.user_id = ? and d.id in (?)";

  // This gives a list of sketch IDs the user did not make.
  public static final String FIND_SKETCHES_NOT_BY_USER = //
  "select id from sketch where user_id <> ?";

  // 1: user.id
  // 2: comma separated list of sketch.id
  public static final String FIND_SKETCHES_BY_USER_DISQUALIFY_CHILDREN = //
  "select distinct sketch_id from description where user_id = ? and sketch_id in (?)";

  // 1: user.id
  // 2: comma separated list of sketch.id
  public static final String FIND_SKETCHES_BY_USER_DISQUALIFY_PARENTS = //
  "select distinct s.id from sketch as s join description as d on d.sketch_id = s.id "
      + "where d.user_id = ? and s.id in (?)";

  // 1: user.id
  //
  // This finds a sequence of sketch -> description -> sketch where the indicated user was not
  // involved. It will give you the id of the youngest sketch, and you can use FIND_JUDGEMENT to get
  // the rest of the info.
  public static final String FIND_SKETCHES_FOR_JUDGEMENT = //
  "select s1.id from sketch as s1 " + //
      "join description as d on d.id = s1.description_id " + // 
      "join sketch as s2 on s2.id = d.sketch_id " + //
      "where s1.user_id <> ? and d.user_id <> ? and s2.user_id <> ?";

  // 1: sketch.id
  //
  // Gives you the id and data for a sketch and its sketch grandparent. It is best to use the
  // FIND_SKETCHES_FOR_JUDGEMENT query to find a valid sketch id to provide for this query.
  public static final String FIND_JUDGEMENT = //
  "select s1.id, s1.data, s2.id, s2.data from sketch as s1 " + // 
      "join description as d on d.id = s1.description_id " + // 
      "join sketch as s2 on s2.id = d.sketch_id " + //
      "where s1.id = ?";

  // 1: user.id
  // 2: comma separated list of sketch.id
  //
  // Gives you the ids of judgements that a user has made for the given set of sketches. Use this
  // to disqualify some sketches. The returned IDs are the subset of provided sketch ids to avoid.
  public static final String FIND_JUDGEMENTS_BY_USER_DISQUALIFY = //
  "select sketch_id_a from judgement where user_id = ? and sketch_id_a in (?)";

  // 1: sketch.id (the 'youngest' one)
  // 2: the rating value, 1 to 5, with 5 the best.
  // 3: user.id (player who issued the rating)
  public static final String INSERT_JUDGEMENT = //
  "insert into judgement (sketch_id, rating, user_id) values (?,?,?)";

  // 1: user_id
  public static final String FIND_TODO_DESCRIPTION = //
  "select id, describe_id from todo_describe where user_id = ? order by describe_id desc limit 1";

  // 1: user_id
  // 2: limit: return the N-1th row in the result set.
  public static final String FIND_TODO_DESCRIPTION_AT_LIMIT = //
  "select id, describe_id from todo_describe where user_id = ? order by describe_id desc limit ?,1";

  public static final String DELETE_TODO_DESCRIPTION = //
  "delete from todo_describe where id = ?";

  // 1: user.id
  // 2: limit: return the N-1th row in the result set.
  public static final String FIND_TODO_SKETCH = // 
  "select id, sketch_id from todo_sketch where user_id = ? order by sketch_id desc limit 1";

  public static final String FIND_TODO_SKETCH_AT_LIMIT = // 
    "select id, sketch_id from todo_sketch where user_id = ? order by sketch_id desc limit ?,1";

  public static final String FIND_TODO_JUDGEMENT = //
  "select id, sketch_id from todo_rate where user_id = ? limit 1";

  public static final String DELETE_TODO_RATE = //
  "delete from todo_rate where id = ?";

  // 1: todo_sketch.id  (THIS IS NOT A SKETCH ID)
  public static final String DELETE_TODO_SKETCH = //
  "delete from todo_sketch where id = ?";

  // 1: user.id
  public static final String FIND_SCORE = //
    "select score from user where id = ?";

  // 1: new score value
  // 2: user.id
  public static final String UPDATE_SCORE = //
    "update user set score = ? where id = ?";

  // 1: sketch.id
  // Gives you the sketch's parent's user id.
  public static final String FIND_SKETCH_PARENT_USER = //
    "select d.user_id from description as d join sketch as s on " + //
    "d.id = s.description_id where s.id = ?";

  // 1: sketch.id
  public static final String FIND_SKETCH_USER = //
    "select user_id from sketch where id = ?";

  // 1: user.id
  public static final String COUNT_TODO_RATE = //
    "SELECT count(id) FROM todo_rate where user_id = ?";
  
  // 1: user.id
  public static final String COUNT_TODO_SKETCH = //
    "SELECT count(id) FROM todo_sketch where user_id = ?";
  
  // 1.user.id
  public static final String COUNT_TODO_DESCRIBE = //
    "SELECT count(id) FROM todo_describe where user_id = ?";
  
  // 1: sketch.id
  public static final String FIND_RATING_FOR_SKETCH = //
    "SELECT AVG(rating), COUNT(rating) FROM judgement where sketch_id=?";
  
  // 1: sketch.id
  //
  // This gives you a list of descriptions that are based on the specified sketch.
  public static final String FIND_SKETCH_CHILDREN = //
    "select id from description where sketch_id = ?";
  
  // 1: description.id
  //
  // This gives you a list of sketches that are based on the specified description.
  public static final String FIND_DESCRIPTION_CHILDREN = //
  "select id from sketch where description_id = ?";

  // gives you the ID of every sketch in the db beginning with the most recent one.
  public static final String FIND_SKETCHES = //
    "select id from sketch order by id desc";
  
  // gives you the five most recent sketch IDs.
  public static final String FIND_RECENT_SKETCHES = //
    "select id from sketch order by id desc limit 5";
}

// http://ca.youtube.com/watch?v=6V_DsL1x1uY
